package com.excel.analysis;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class ReaderExcel {

	private Workbook wb = null;

	private InputStream is = null;

	private String fileType = null;

	private int numberOfSheets; // sheet数量

	private List<ExcelBean> kpi = null;

	/**
	 * 构造函数
	 * 
	 * @param filePath
	 * @throws Exception
	 */
	public ReaderExcel(String filePath) throws Exception {
		fileType = filePath.substring(filePath.lastIndexOf(".") + 1);
		is = new FileInputStream(filePath);
		getWorkbook();
	}

	/**
	 * 获得workbook
	 * 
	 * @throws Exception
	 */
	public Workbook getWorkbook() throws Exception {
		if (is == null) {
			throw new IOException("no file"); //
		}
		if (fileType == null
				|| (!fileType.equals("xls") && !fileType.equals("xlsx"))) {
			throw new IOException("The file is not excel"); // 文件是否以xls,xlsx结尾
		}
		wb = WorkbookFactory.create(is); // 创建workbook
		numberOfSheets = wb.getNumberOfSheets(); // sheets数量
		is.close(); // 关闭流
		return wb;
	}

	/**
	 * 获取指定excel工作薄
	 * 
	 * @param index
	 * @return
	 */
	public Sheet getSheetAt(int index) throws Exception {
		if (index >= numberOfSheets) {
			throw new Exception("index too larger");
		}
		return wb.getSheetAt(index);
	}

	/**
	 * 工作薄名称获得sheet
	 * 
	 * @param sheetName
	 * @return
	 * @throws Exception
	 */
	public Sheet getSheetAt(String sheetName) throws Exception {
		return wb.getSheet(sheetName);
	}

	/**
	 * 解析Excel,对Excel解析处理按行来进行处理
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<ExcelRow> analysis(int sheetIndex) throws Exception {
		List<ExcelRow> beans = new ArrayList<ExcelRow>();
		// 指标处理
		kpi = new ArrayList<ExcelBean>();

		Sheet sheet = getSheetAt(sheetIndex); // Excel解析工作薄
		int firstRow = sheet.getFirstRowNum();
		int lastRow = sheet.getLastRowNum(); // excel可处理结果行号,excel行处理从0开始
		for (int i = firstRow; i <= lastRow; i++) {
			Row row = sheet.getRow(i); // 获得一行
			ExcelRow excelBean = new ExcelRow();
			beans.add(excelBean);
			int startCellNum = row.getFirstCellNum(); // 列开始号
			int lastCellNum = row.getLastCellNum(); // 结束号
			for (int j = startCellNum; j < lastCellNum; j++) {
				Cell cell = row.getCell(j); // 获得列
				String value = getCellValue(cell);
				if (j == startCellNum) {
					excelBean.setValue(value);
				}
				CellRangeAddress range = handlerRegion(sheet, cell); // 判断是否为合并单元格
				if (range != null) {
					// 该单元格是合并单元格,并且开始范围在单元格
					if (cell.getRowIndex() == range.getFirstRow()
							&& cell.getColumnIndex() == range.getFirstColumn()) {
						ExcelBean b = createColumn(range.getFirstRow(),
								range.getLastRow(), range.getFirstColumn(),
								range.getLastColumn(), value);
						excelBean.addBean(b);
						if (j == startCellNum) {
							if (ExpressionUtils.isKpi(value)) {
								kpi.add(b);
								handlerRow(b, sheet, range.getFirstRow(),
										range.getLastRow() + 1, 1);
							}
						}
					}
				} else {
					ExcelBean b = createColumn(cell.getRowIndex(),
							cell.getRowIndex(), cell.getColumnIndex(),
							cell.getColumnIndex(), value);
					excelBean.addBean(b);
				}
			}
		}
		return beans;
	}

	/**
	 * 
	 * 
	 * @param bean
	 * @param sheet
	 * @param startRow
	 * @param endRow
	 * @throws Exception
	 */
	public void handlerRow(ExcelBean bean, Sheet sheet, int startRow,
			int endRow, int columnNum) throws Exception {
		for (int i = startRow; i < endRow;) {
			Row row = sheet.getRow(i);
			int column = row.getLastCellNum();
			if (columnNum >= column) {
				break;
			}
			Cell cell = row.getCell(columnNum);
			String value = getCellValue(cell);
			CellRangeAddress range = handlerRegion(sheet, cell); // 判断是否为合并单元格
			if (range != null) {
				if (value != null && ExpressionUtils.isKpi(value)) {
					ExcelBean b = createColumn(range.getFirstRow(),
							range.getLastRow(), range.getFirstColumn(),
							range.getLastColumn(), value);
					bean.addBean(b);
					if (range.getLastColumn() + 1 < column) {
						handlerRow(b, sheet, range.getFirstRow(),
								range.getLastRow() + 1,
								range.getLastColumn() + 1);
					}
				} else if (value != null && ExpressionUtils.isEntry(value)) {
					ExcelBean b = createColumn(range.getFirstRow(),
							range.getLastRow(), range.getFirstColumn(),
							range.getLastColumn(), value);
					bean.addBean(b);
				} else {
					ExcelBean b = createColumn(range.getFirstRow(),
							range.getLastRow(), range.getFirstColumn(),
							range.getLastColumn(), "");
					if (range.getLastColumn() + 1 < column) {
						handlerRow(b, sheet, range.getFirstRow(),
								range.getLastRow() + 1,
								range.getLastColumn() + 1);
					}
				}

				if (range.getFirstRow() != range.getLastRow()) {
					i = range.getLastRow() + 1;
				} else {
					i++;
				}
			} else {
				if (value != null && ExpressionUtils.isKpi(value)) {
					ExcelBean b = createColumn(cell.getRowIndex(),
							cell.getRowIndex(), cell.getColumnIndex(),
							cell.getColumnIndex(), value);
					bean.addBean(b);
					if (columnNum + 1 < column) {
						handlerRow(b, sheet, cell.getRowIndex(),
								cell.getRowIndex() + 1, columnNum + 1);
					}
				} else if (value != null && ExpressionUtils.isEntry(value)) {
					ExcelBean b = createColumn(cell.getRowIndex(),
							cell.getRowIndex(), cell.getColumnIndex(),
							cell.getColumnIndex(), value);
					bean.addBean(b);
				} else {
					ExcelBean b = createColumn(cell.getRowIndex(),
							cell.getRowIndex(), cell.getColumnIndex(),
							cell.getColumnIndex(), "");
					if (columnNum + 1 < column) {
						handlerRow(b, sheet, cell.getRowIndex(),
								cell.getRowIndex() + 1, columnNum + 1);
					}
				}
				i++;
			}
		}
	}

	/**
	 * 单元格内的值
	 * 
	 * @param cell
	 * @return
	 * @throws Exception
	 */
	private String getCellValue(Cell cell) throws Exception {
		String value = "";
		int type = cell.getCellType(); // 单元格类型
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = null;
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue() ? "Y" : "N";
			break;
		case Cell.CELL_TYPE_ERROR:
			value = "error";
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			value = String.valueOf(cell.getNumericCellValue());
			break;
		}
		return value;
	}

	/**
	 * 是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private CellRangeAddress handlerRegion(Sheet sheet, Cell cell) {
		int row = cell.getRowIndex();
		int column = cell.getColumnIndex();
		int sheetMergeCount = sheet.getNumMergedRegions();
		boolean isMerged = false;
		CellRangeAddress region = null;
		for (int i = 0; i < sheetMergeCount; i++) {
			region = sheet.getMergedRegion(i);
			int firstColumn = region.getFirstColumn();
			int lastColumn = region.getLastColumn();
			int firstRow = region.getFirstRow();
			int lastRow = region.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					isMerged = true;
					break;
				}
			}
		}
		if (isMerged) {
			// 是合并单元格
			return region;
		} else {
			return null;
		}
	}

	/**
	 * 构建excelcolumn
	 * 
	 * @param startRow
	 * @param endRow
	 * @param startColumn
	 * @param endColumn
	 * @param merge
	 * @param value
	 * @return
	 */
	private ExcelBean createColumn(int startRow, int endRow, int startColumn,
			int endColumn, String value) {
		ExcelBean ec = new ExcelBean();
		ec.setStartRow(startRow);
		ec.setEndRow(endRow);
		ec.setStartColumn(startColumn);
		ec.setEndColumn(endColumn);
		ec.setValue(value);
		ec.setValue(value);
		return ec;
	}

	public List<ExcelBean> getKpi() {
		return kpi;
	}

}
